package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;

import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreSurveyQuestion;
import com.edu.hre.db.dto.HreSurveyQuestionAnswer;
import com.edu.hre.db.dto.HreSurveyTopic;

public class SurveyDAO extends BaseDAO {

	private static final Logger logger = Logger.getLogger(SurveyDAO.class);

	public List<HreSurveyTopic> getAllSurvey(int userSubUnitId, int userId) {
		if (userSubUnitId == 0)
			userSubUnitId = 1;
		List<Integer> listSubUnit = new ArrayList<Integer>();
		listSubUnit.add(userSubUnitId);
		List<HreSurveyTopic> listSurveys = new ArrayList<HreSurveyTopic>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			Integer subUnit = userSubUnitId;
			while (subUnit != 0) {
				conn = DBPool.getConnection();
				String strSQL = "select * from hre_subunit WHERE id = "+subUnit;
				preStmt = conn.prepareStatement(strSQL);
				rs = preStmt.executeQuery();
				while (rs.next()) {
					listSubUnit.add(rs.getInt("parent_id"));
					subUnit = rs.getInt("parent_id");
				}
			}
			String strListIdSubUnit = "(";
			for (Iterator iterator = listSubUnit.iterator(); iterator.hasNext();) {
				Integer id = (Integer) iterator
						.next();
				strListIdSubUnit += id+",";
			}
			strListIdSubUnit = strListIdSubUnit.substring(0,
					strListIdSubUnit.length() - 1);
			strListIdSubUnit +=")";
			
			String strSQLQuery = "select *"
					+ " FROM hre_survey_topic t"
					+ " LEFT JOIN hre_survey_emp_answer ea ON ea.topic_id = t.id AND ea.user_id = "
					+ userId + " WHERE (t.subunit_id IN " + strListIdSubUnit
					+ " OR t.show_to_all = 1)"
					+ " AND t.start_date <= CURDATE()"
					+ "  AND t.end_date >= CURDATE()" + " AND ea.id IS NULL";
			preStmt = conn.prepareStatement(strSQLQuery);
			System.out.println(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreSurveyTopic topic = new HreSurveyTopic();
				topic.setName(rs.getString("name"));
				topic.setStartDate(rs.getDate("start_date"));
				topic.setEndDate(rs.getDate("end_date"));
				topic.setId(rs.getInt("id"));
				listSurveys.add(topic);
			}

		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}

		return listSurveys;
	}

	public List<HreSurveyQuestion> GetSurveyDetails(int topicId) {
		List<HreSurveyQuestion> listQuestions = new ArrayList<HreSurveyQuestion>();
		List<HreSurveyQuestionAnswer> listAnswers = new ArrayList<HreSurveyQuestionAnswer>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT q.text as question_text,"
					+ " q.rank as question_rank,"
					+ " q.type as question_type,"
					+ " a.* "
					+ " FROM hre_survey_question q "
					+ " JOIN hre_survey_question_answer a ON q.id = a.question_id"
					+ " WHERE q.topic_id = " + topicId;
			preStmt = conn.prepareStatement(strSQLQuery);

			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreSurveyQuestionAnswer answer = new HreSurveyQuestionAnswer();
				answer.setId(rs.getInt("id"));
				answer.setLabel(rs.getString("label"));
				answer.setType(rs.getInt("type"));
				answer.setText(rs.getString("text"));
				answer.setQuestionId(rs.getInt("question_id"));
				answer.setQuestionText(rs.getString("question_text"));
				answer.setQuestionRank(rs.getInt("question_rank"));
				answer.setQuestionType(rs.getInt("question_type"));
				listAnswers.add(answer);
			}
			HashSet<Integer> qList = new HashSet<Integer>();
			for (HreSurveyQuestionAnswer answer : listAnswers) {
				if (!qList.contains(answer.getQuestionId())) {
					HreSurveyQuestion question = new HreSurveyQuestion();
					question.setText(answer.getQuestionText().replace("\r\n",
							"<br>"));
					question.setRank(answer.getQuestionRank());
					question.setType(answer.getQuestionType());
					question.setId(answer.getQuestionId());
					question.Answers = new ArrayList<HreSurveyQuestionAnswer>();
					question.Answers.add(answer);
					qList.add(question.getId());
					listQuestions.add(question);
				} else {
					for (HreSurveyQuestion q : listQuestions) {
						if (q.getId().equals(answer.getQuestionId()))
							q.Answers.add(answer);
					}
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}

		return listQuestions;
	}

	public void InsertSurveyAnswer(int topicId, int userId,
			ArrayList<Integer> answerIdAddList, HashMap<Integer, String> m) {
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			conn = DBPool.getConnection();
			StringBuilder sb = new StringBuilder();
			sb.append("INSERT INTO hre_survey_emp_answer(type,answer,freetext,user_id,topic_id) VALUE ");

			for (Integer answerId : answerIdAddList) {
				String freeText;
				int type;
				if (m.containsKey(answerId)) {
					freeText = "'" + m.get(answerId) + "'";
					type = 2;
				} else {
					freeText = "NULL";
					type = 1;
				}
				sb.append("(" + type + "," + answerId + "," + freeText + ","
						+ userId + "," + topicId + "),");
			}

			String strSQLQuery = sb.toString().substring(0,
					sb.toString().length() - 1);
			System.out.println(strSQLQuery);
			preStmt = conn.prepareStatement(strSQLQuery);
			preStmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt);
		}

	}
}
